﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Vegetable.Model;
using OMG.Fill.Vegetable.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Vegetable.Service
{
    /// <summary>
    /// 表5蔬菜生产经营实体表T_VegProsecution通用操作类
    /// </summary>
    public class VegProsecutionServices:VegProsecutions
    {
        DictServices dict = new DictServices();
        public Pager<List<VegProsecutionEntity>> GetPageList(VegProsecutionQuery query)
        {
            Pager<List<VegProsecutionEntity>> obj = new Pager<List<VegProsecutionEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_VegProsecution where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<VegProsecutionEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表5蔬菜生产经营实体表T_VegProsecution分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 蔬菜外销情况表----数据填报(数据更新)
        /// </summary>
        /// <returns></returns>
        public ResResult<VegProsecutionEntity> UpdateModelList(VegProsecutionEntity model)
        {
            model.UpdateTime = DateTime.Now;
            ResResult<VegProsecutionEntity> result = new ResResult<VegProsecutionEntity>();
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_VegProsecution set ";
            strSql += "ReportId = @ReportId,";
            strSql += "PFSC_Num = @PFSC_Num,";
            strSql += "PFSC_Cd = @PFSC_Cd,";
            strSql += "PFSC_Zy = @PFSC_Zy,";
            strSql += "PFSC_Qy = @PFSC_Qy,";
            strSql += "VegCompanyNum = @VegCompanyNum,";
            strSql += "ZzNum_Country = @ZzNum_Country,";
            strSql += "ZzNum_Pro = @ZzNum_Pro,";
            strSql += "ZzNum_City = @ZzNum_City,";
            strSql += "ZzNum_Area = @ZzNum_Area,";
            strSql += "ZzNum_Other = @ZzNum_Other,";
            strSql += "VegHzNum = @VegHzNum,";
            strSql += "VegNhNum = @VegNhNum,"; 
             strSql += "VegHzSale = @VegHzSale,";
             strSql += "VegMjNum = @VegMjNum,";
            strSql += "JTNC_Num = @JTNC_Num,";
            strSql += "JTNC_NMNum = @JTNC_NMNum,";
            strSql += "JTNC_Area = @JTNC_Area,";
            strSql += "JTNC_Sales = @JTNC_Sales,";
            strSql += "ZlrzNum_Sfx = @ZlrzNum_Sfx,";
            strSql += "ZlrzNum_Sfq = @ZlrzNum_Sfq,";
            strSql += "ZlrzNum_Cmsbs = @ZlrzNum_Cmsbs,";
            strSql += "ZlrzNum_Mpcp = @ZlrzNum_Mpcp,";
            strSql += "ZlrzNum_YnsZmsb = @ZlrzNum_YnsZmsb,";
            strSql += "ZlrzNum_WghNum = @ZlrzNum_WghNum,";
            strSql += "ZlrzNum_Wghmj = @ZlrzNum_Wghmj,";
            strSql += "ZlrzNum_Greencp = @ZlrzNum_Greencp,";
            strSql += "ZlrzNum_Greencpmj = @ZlrzNum_Greencpmj,";
            strSql += "ZlrzNum_Dlsb = @ZlrzNum_Dlsb,";
            strSql += "ZlrzNum_Dlbh = @ZlrzNum_Dlbh,";
            strSql += "Remark = @Remark,";
            strSql += "AddUser = @AddUser,";
            strSql += "AddTime = @AddTime,";
            strSql += "UpdateUser = @UpdateUser,";
            strSql += "UpdateTime = @UpdateTime";
            strSql += " where Id = @Id";
            if (model != null)
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    using (var trans = dbCon.BeginTransaction())
                    {
                        try
                        {
                            dbCon.Execute(strSql, model, trans);
                            result.message = "更新蔬菜基本情况数据成功。";
                            result.status = DefaultKey.SuccessResult;
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            result.message = "更新蔬菜基本情况异常,异常信息:" + ex.ToString().Trim();
                            result.status = DefaultKey.ErrorResult;
                            LogHelper.WriteLog("更新蔬菜基本情况异常,异常信息:" + ex.ToString().Trim());
                            trans.Rollback();
                        }
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_VegProsecution where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表1蔬菜生产实体情况表T_VegProsecution对象信息失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 获取指定对象
        /// </summary>
        public override ResResult<VegProsecutionEntity> GetModel(string ReportId)
        {
            ResResult<VegProsecutionEntity> obj = new ResResult<VegProsecutionEntity>();
            VegProsecutionEntity model = new VegProsecutionEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = model;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_VegProsecution where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<VegProsecutionEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表1蔬菜生产实体情况调查T_VegProsecution对象信息失败", ex);
                }
            }
            return obj;
        }

        /// <summary>
        /// 获取今往年数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<VegProsecutionViewModel> GetModel(VegNowModel model)
        {
            ResResult<VegProsecutionViewModel> obj = new ResResult<VegProsecutionViewModel>();
            VegProsecutionEntity Entity = new VegProsecutionEntity();
            VegProsecutionViewModel viewModel = new VegProsecutionViewModel();
            var paramlist = new DynamicParameters();
            string strSqlReport = "";
            if (model != null)
            {
                if (!string.IsNullOrEmpty(model.AreaCode) && !string.IsNullOrEmpty(model.ReportCode) && !string.IsNullOrEmpty(model.FillInYear))
                {
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("ReportCode", model.ReportCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    int OldFillInYear = Convert.ToInt32(model.FillInYear) - 1;
                    if (OldFillInYear < 0)
                    {
                        OldFillInYear = 0;
                    }
                    paramlist.Add("OldFillInYear", OldFillInYear.ToString());
                    string strNowSql = "SELECT vf.Id,vf.ReportId,PFSC_Num, PFSC_Cd, PFSC_Zy, PFSC_Qy, VegCompanyNum, ZzNum_Country, ZzNum_Pro , ZzNum_City";
                    strNowSql += ", ZzNum_Area, ZzNum_Other , VegHzNum , VegNhNum, VegMjNum,VegHzSale, JTNC_Num , JTNC_NMNum, JTNC_Area , JTNC_Sales, ZlrzNum_Sfx, ZlrzNum_Sfq";
                    strNowSql += ", ZlrzNum_Cmsbs, ZlrzNum_Mpcp, ZlrzNum_YnsZmsb, ZlrzNum_WghNum, ZlrzNum_Wghmj, ZlrzNum_Greencp, ZlrzNum_Greencpmj, ZlrzNum_Dlsb";
                    strNowSql += ", ZlrzNum_Dlbh , Remark, vf.AddUser , vf.AddTime, vf.UpdateUser, vf.UpdateTime,ReportCode,Status as ReportStatus ";
                    strNowSql += " FROM T_Report r LEFT JOIN T_VegProsecution vf on vf.ReportId = r.Id ";
                    strNowSql += " WHERE ReportCode = @ReportCode and FillInYear = @FillInYear and AreaCode = @AreaCode ";
                    string strOldSql = "SELECT vf.Id as OldId,vf.ReportId as OldReportId,PFSC_Num as OldPFSC_Num, PFSC_Cd as OldPFSC_Cd, PFSC_Zy as OldPFSC_Zy, PFSC_Qy as OldPFSC_Qy, VegCompanyNum as OldVegCompanyNum, ZzNum_Country as OldZzNum_Country, ZzNum_Pro OldZzNum_Pro , ZzNum_City as OldZzNum_City";
                    strOldSql += ", ZzNum_Area as OldZzNum_Area, ZzNum_Other OldZzNum_Other , VegHzNum OldVegHzNum , VegNhNum as  OldVegNhNum,VegMjNum as OldVegMjNum,VegHzSale OldVegHzSale, JTNC_Num as OldJTNC_Num , JTNC_NMNum as OldJTNC_NMNum, JTNC_Area as OldJTNC_Area , JTNC_Sales as OldJTNC_Sales, ZlrzNum_Sfx as OldZlrzNum_Sfx, ZlrzNum_Sfq as OldZlrzNum_Sfq";
                    strOldSql += ", ZlrzNum_Cmsbs as OldZlrzNum_Cmsbs, ZlrzNum_Mpcp as OldZlrzNum_Mpcp, ZlrzNum_YnsZmsb as OldZlrzNum_YnsZmsb, ZlrzNum_WghNum as OldZlrzNum_WghNum, ZlrzNum_Wghmj as OldZlrzNum_Wghmj, ZlrzNum_Greencp as OldZlrzNum_Greencp, ZlrzNum_Greencpmj as OldZlrzNum_Greencpmj, ZlrzNum_Dlsb as OldZlrzNum_Dlsb";
                    strOldSql += ", ZlrzNum_Dlbh as OldZlrzNum_Dlbh , Remark as OldRemark, vf.AddUser as OldAddUser , vf.AddTime as OldAddTime, vf.UpdateUser as OldUpdateUser, vf.UpdateTime as OldUpdateTime,ReportCode as ReportCode,Status as OldReportStatus ";
                    strOldSql += " FROM T_Report r LEFT JOIN T_VegProsecution vf on vf.ReportId = r.Id ";
                    strOldSql += " WHERE ReportCode = @ReportCode and FillInYear = @OldFillInYear and AreaCode = @AreaCode ";
                    strSqlReport = "SELECT * FROM ((" + strNowSql + ")t LEFT JOIN  (" + strOldSql + ")tt ON t.ReportCode = tt.ReportCode)";

                    //判断选择的年度与上一年度是否生成过报表
                    if (CheckRepCreate(model.ReportCode, model.AreaCode, model.FillInYear) && CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString()))
                    {
                        obj.model = viewModel;
                        obj.status = -1;
                        DictServices dictServices = new DictServices();
                        obj.message = dictServices.GetModel("ReportYear", model.FillInYear).model.DictText + "的报表无需填报！！";
                        return obj;
                    }
                    //判断本年是否生成过报表
                    else if (CheckRepCreate(model.ReportCode, model.AreaCode, model.FillInYear))
                    {
                        var dbCon = ConnectFactory.GetOpenConnection();
                        obj.model = dbCon.Query<VegProsecutionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                        obj.status = -2;
                        DictServices dictServices = new DictServices();
                        obj.message = dictServices.GetModel("ReportYear", model.FillInYear).model.DictText + "的报表无需填报！！";
                        dbCon.Dispose();//数据库连接释放
                        return obj;
                    }
                    else
                    {
                        //获取报表Id
                        Entity.ReportId = GetReportId(model.ReportCode, model.AreaCode, model.FillInYear);
                        //判断表T_VegFacility是否初始化过
                        if (CheckRepCount(Entity.ReportId))
                        {
                            if (AddInfo(Entity).status > 0)//报表初始化
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    //查询去年数据
                                    obj.model = dbCon.Query<VegProsecutionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 1;//初始化成功status为1
                                    obj.message = "T_VegProsecution报表初始化成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                        }
                        else
                        {
                            try
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    obj.model = dbCon.Query<VegProsecutionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 2;//成功状态为2
                                    obj.message = "获取T_VegProsecution报表今往年数据成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                            catch (Exception ex)
                            {
                                obj.status = 0; //失败状态
                                obj.message = ex.ToString().Trim();
                                LogHelper.ErrorLog("获取T_VegProsecution报表今往年数据失败", ex);
                            }
                        }
                    }
                }
                else
                {
                    obj.message = "当前地区没有对应报表";
                    obj.status = 0;
                }
            }
            else
            {
                obj.message = "参数错误,请与管理员联系!!!";
                obj.status = 0;
            }
            return obj;
        }


        /// <summary>
        /// 根据地区、报表代码、填报年度判断是否生成过报表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckRepCreate(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select count(1) from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_VegProsecution没有生成过", ex);
                return false;
            }
        }


        /// <summary>
        /// 根据地区、报表代码、填报年度获取报表Id
        /// </summary>
        /// <param name="ReportCode"></param>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public string GetReportId(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    return dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Id;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_VegProsecution没有生成过", ex);
                return "0";
            }
        }


        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<VegProsecutionModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<VegProsecutionModel> list = new List<VegProsecutionModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(3);//从第四行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                VegProsecutionModel model = new VegProsecutionModel();
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    
                    //if (i == 3)
                    //{
                    //    model.year = row.GetCell(3).ToString();
                    //}
                    if (i == 3)//蔬菜市场
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.PFSC_Num = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.PFSC_Num = null;
                        }
                    }
                    if (i == 4)//产地批发市场
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.PFSC_Cd = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.PFSC_Cd = null;
                        }
                    }
                    if (i == 5)//专业批发市场
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.PFSC_Zy = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.PFSC_Zy = null;
                        }
                    }
                    if (i == 6)//区域性综合批发市场
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.PFSC_Qy = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.PFSC_Qy = null;
                        }
                    }
                    if (i == 7)//蔬菜企业个数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.VegCompanyNum = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.VegCompanyNum = null;
                        }
                    }
                    if (i == 8)//国家级龙头企业个数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZzNum_Country = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZzNum_Country = null;
                        }
                    }
                    if (i == 9)//省级龙头企业个数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZzNum_Pro = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZzNum_Pro = null;
                        }
                    }
                    if (i == 10)//州（市）级龙头企业
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZzNum_City = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZzNum_City = null;
                        }
                    }
                    if (i == 11)//县（市、区）级龙头企业
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZzNum_Area = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZzNum_Area = null;
                        }
                    }
                    if (i == 12)//其他龙头企业
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZzNum_Other = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZzNum_Other = null;
                        }
                    }
                    if (i == 14)//蔬菜农名合作社数量
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.VegHzNum = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.VegHzNum = null;
                        }
                    }
                    if (i == 15)//参加蔬菜农民合作社农户数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.VegNhNum = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.VegNhNum = null;
                        }
                    }
                    if (i == 16)//蔬菜农民合作社经营面积（其中：流转土地面积）
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.VegMjNum = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.VegMjNum = null;
                        }
                    }
                    if (i == 17)//蔬菜农民合作社年销售收入
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.VegHzSale = decimal.Parse(row.GetCell(3).ToString());
                        }

                        else
                        {
                            model.VegHzSale = null;
                        }
                    }
                    if (i == 19)//蔬菜家庭农场数量
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.JTNC_Num = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.JTNC_Num = null;
                        }
                    }
                    if (i == 20)//参加蔬菜家庭农场农户数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.JTNC_NMNum = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.JTNC_NMNum = null;
                        }
                    }
                    if (i == 21)//蔬菜家庭农场经营面积（其中：流转土地面积）
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.JTNC_Area = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.JTNC_Area = null;
                        }
                    }
                    if (i == 22)//蔬菜家庭农场年销售收入
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.JTNC_Sales = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.JTNC_Sales = null;
                        }
                    }
                    if (i == 24)//国家级农业标准化示范县数量
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Sfx = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Sfx = null;
                        }
                    }
                    if (i == 25)//出口食品农产品质量安全示范区数量
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Sfq = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Sfq = null;
                        }
                    }
                    if (i == 26)//中国驰名商标数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Cmsbs = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Cmsbs = null;
                        }
                    }
                    if (i == 27)//中国名牌产品数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Mpcp = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Mpcp = null;
                        }
                    }
                    if (i == 28)//云南省著名商标数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_YnsZmsb = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_YnsZmsb = null;
                        }
                    }
                    if (i == 29)//无公害农产品数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_WghNum = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_WghNum = null;
                        }
                    }
                    if (i == 30)//无公害农产品认证面积
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Wghmj = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Wghmj = null;
                        }
                    }
                    if (i == 31)//绿色产品数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Greencp = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Greencp = null;
                        }
                    }
                    if (i == 32)//绿色产品认证面积
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Greencpmj = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Greencpmj = null;
                        }
                    }
                    if (i == 33)//地理标志证明商标数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Dlsb = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Dlsb = null;
                        }
                    }
                    if (i == 34)//地理标志产品保护认证数
                    {
                        if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                        {
                            model.ZlrzNum_Dlbh = decimal.Parse(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZlrzNum_Dlbh = null;
                        }
                    }
                }
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(VegProsecutionModel model)
        {
            model.AddTime = DateTime.Now;
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_VegProsecution";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "PFSC_Num,";
            strSql += "PFSC_Cd,";
            strSql += "PFSC_Zy,";
            strSql += "PFSC_Qy,";
            strSql += "VegCompanyNum,";
            strSql += "ZzNum_Country,";
            strSql += "ZzNum_Pro,";
            strSql += "ZzNum_City,";
            strSql += "ZzNum_Area,";
            strSql += "ZzNum_Other,";
            strSql += "VegHzNum,";
            strSql += "VegNhNum,";
            strSql += "VegMjNum,"; 
             strSql += "VegHzSale,";
             strSql += "JTNC_Num,";
            strSql += "JTNC_NMNum,";
            strSql += "JTNC_Area,";
            strSql += "JTNC_Sales,";
            strSql += "ZlrzNum_Sfx,";
            strSql += "ZlrzNum_Sfq,";
            strSql += "ZlrzNum_Cmsbs,";
            strSql += "ZlrzNum_Mpcp,";
            strSql += "ZlrzNum_YnsZmsb,";
            strSql += "ZlrzNum_WghNum,";
            strSql += "ZlrzNum_Wghmj,";
            strSql += "ZlrzNum_Greencp,";
            strSql += "ZlrzNum_Greencpmj,";
            strSql += "ZlrzNum_Dlsb,";
            strSql += "ZlrzNum_Dlbh,";
            strSql += "Remark,";
            strSql += "AddUser,";
            strSql += "AddTime,";
            strSql += "UpdateUser,";
            strSql += "UpdateTime)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@VegCompanyNum,";
            strSql += "@ZzNum_Country,";
            strSql += "@ZzNum_Pro,";
            strSql += "@ZzNum_City,";
            strSql += "@ZzNum_Area,";
            strSql += "@ZzNum_Other,";
            strSql += "@VegHzNum,";
            strSql += "@VegNhNum,";
            strSql += "@VegMjNum,"; 
             strSql += "@VegHzSale,";
             strSql += "@JTNC_Num,";
            strSql += "@JTNC_NMNum,";
            strSql += "@JTNC_Area,";
            strSql += "@JTNC_Sales,";
            strSql += "@ZlrzNum_Sfx,";
            strSql += "@ZlrzNum_Sfq,";
            strSql += "@ZlrzNum_Cmsbs,";
            strSql += "@ZlrzNum_Mpcp,";
            strSql += "@ZlrzNum_YnsZmsb,";
            strSql += "@ZlrzNum_WghNum,";
            strSql += "@ZlrzNum_Wghmj,";
            strSql += "@ZlrzNum_Greencp,";
            strSql += "@ZlrzNum_Greencpmj,";
            strSql += "@ZlrzNum_Dlsb,";
            strSql += "@ZlrzNum_Dlbh,";
            strSql += "@Remark,";
            strSql += "@AddUser,";
            strSql += "@AddTime,";
            strSql += "@UpdateUser,";
            strSql += "@UpdateTime)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增表5蔬菜生产经营实体表T_VegProsecution对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(VegNowModel model)
        {
            model.FillInYear = dict.GetKey("ReportYear", model.FillInYear);
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("VegProsecution") as HSSFSheet;//读取模板所在sheet位置
           VegProsecutionViewModel pageModel = GetModel(model).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式
            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(3);
            datarow2.GetCell(3).SetCellValue(pageModel.PFSC_Num == null ? "" : Common.GetADecimal(pageModel.PFSC_Num));
            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(4);
            datarow3.GetCell(3).SetCellValue(pageModel.PFSC_Cd == null ? "" : Common.GetADecimal(pageModel.PFSC_Cd));
            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(5);
            datarow4.GetCell(3).SetCellValue(pageModel.PFSC_Zy == null ? "" : Common.GetADecimal(pageModel.PFSC_Zy));
            HSSFRow datarow5 = (HSSFRow)sheet.GetRow(6);
            datarow5.GetCell(3).SetCellValue(pageModel.PFSC_Qy == null ? "" : Common.GetADecimal(pageModel.PFSC_Qy));
            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(7);
            datarow6.GetCell(3).SetCellValue(pageModel.VegCompanyNum == null ? "" : Common.GetADecimal(pageModel.VegCompanyNum));
            HSSFRow datarow7 = (HSSFRow)sheet.GetRow(8);
            datarow7.GetCell(3).SetCellValue(pageModel.ZzNum_Country == null ? "" : Common.GetADecimal(pageModel.ZzNum_Country));
            HSSFRow datarow8 = (HSSFRow)sheet.GetRow(9);
            datarow8.GetCell(3).SetCellValue(pageModel.ZzNum_Pro == null ? "" : Common.GetADecimal(pageModel.ZzNum_Pro));
            HSSFRow datarow9 = (HSSFRow)sheet.GetRow(10);
            datarow9.GetCell(3).SetCellValue(pageModel.ZzNum_City == null ? "" : Common.GetADecimal(pageModel.ZzNum_City));
            HSSFRow datarow10 = (HSSFRow)sheet.GetRow(11);
            datarow10.GetCell(3).SetCellValue(pageModel.ZzNum_Area == null ? "" : Common.GetADecimal(pageModel.ZzNum_Area));
            HSSFRow datarow11 = (HSSFRow)sheet.GetRow(12);
            datarow11.GetCell(3).SetCellValue(pageModel.ZzNum_Other == null ? "" : Common.GetADecimal(pageModel.ZzNum_Other));
            HSSFRow datarow12 = (HSSFRow)sheet.GetRow(14);
            datarow12.GetCell(3).SetCellValue(pageModel.VegHzNum == null ? "" : Common.GetADecimal(pageModel.VegHzNum));
            HSSFRow datarow13 = (HSSFRow)sheet.GetRow(15);
            datarow13.GetCell(3).SetCellValue(pageModel.VegNhNum == null ? "" : Common.GetADecimal(pageModel.VegNhNum));
            HSSFRow datarow14 = (HSSFRow)sheet.GetRow(16);
            datarow14.GetCell(3).SetCellValue(pageModel.VegMjNum == null ? "" : Common.GetADecimal(pageModel.VegMjNum));
            HSSFRow datarow15 = (HSSFRow)sheet.GetRow(17);
            datarow15.GetCell(3).SetCellValue(pageModel.VegHzSale == null ? "" : Common.GetADecimal(pageModel.VegHzSale));
            HSSFRow datarow16 = (HSSFRow)sheet.GetRow(19);
            datarow16.GetCell(3).SetCellValue(pageModel.JTNC_Num == null ? "" : Common.GetADecimal(pageModel.JTNC_Num));
            HSSFRow datarow17 = (HSSFRow)sheet.GetRow(20);
            datarow17.GetCell(3).SetCellValue(pageModel.JTNC_NMNum == null ? "" : Common.GetADecimal(pageModel.JTNC_NMNum));
            HSSFRow datarow18 = (HSSFRow)sheet.GetRow(21);
            datarow18.GetCell(3).SetCellValue(pageModel.JTNC_Area == null ? "" : Common.GetADecimal(pageModel.JTNC_Area));
            HSSFRow datarow19 = (HSSFRow)sheet.GetRow(22);
            datarow19.GetCell(3).SetCellValue(pageModel.JTNC_Sales == null ? "" : Common.GetADecimal(pageModel.JTNC_Sales));
            HSSFRow datarow20 = (HSSFRow)sheet.GetRow(24);
            datarow20.GetCell(3).SetCellValue(pageModel.ZlrzNum_Sfx == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Sfx));
            HSSFRow datarow21 = (HSSFRow)sheet.GetRow(25);
            datarow21.GetCell(3).SetCellValue(pageModel.ZlrzNum_Sfq == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Sfq));
            HSSFRow datarow22 = (HSSFRow)sheet.GetRow(26);
            datarow22.GetCell(3).SetCellValue(pageModel.ZlrzNum_Cmsbs == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Cmsbs));
            HSSFRow datarow23 = (HSSFRow)sheet.GetRow(27);
            datarow23.GetCell(3).SetCellValue(pageModel.ZlrzNum_Mpcp == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Mpcp));
            HSSFRow datarow24 = (HSSFRow)sheet.GetRow(28);
            datarow24.GetCell(3).SetCellValue(pageModel.ZlrzNum_YnsZmsb == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_YnsZmsb));
            HSSFRow datarow25 = (HSSFRow)sheet.GetRow(29);
            datarow25.GetCell(3).SetCellValue(pageModel.ZlrzNum_WghNum == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_WghNum));
            HSSFRow datarow26 = (HSSFRow)sheet.GetRow(30);
            datarow26.GetCell(3).SetCellValue(pageModel.ZlrzNum_Wghmj == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Wghmj));
            HSSFRow datarow27 = (HSSFRow)sheet.GetRow(31);
            datarow27.GetCell(3).SetCellValue(pageModel.ZlrzNum_Greencp == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Greencp));
            HSSFRow datarow28 = (HSSFRow)sheet.GetRow(32);
            datarow28.GetCell(3).SetCellValue(pageModel.ZlrzNum_Greencpmj == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Greencpmj));
            HSSFRow datarow29 = (HSSFRow)sheet.GetRow(33);
            datarow29.GetCell(3).SetCellValue(pageModel.ZlrzNum_Dlsb == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Dlsb));
            HSSFRow datarow30 = (HSSFRow)sheet.GetRow(34);
            datarow30.GetCell(3).SetCellValue(pageModel.ZlrzNum_Dlbh == null ? "" : Common.GetADecimal(pageModel.ZlrzNum_Dlbh));

            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        public virtual ResResult<int> UpdateInfo(VegProsecutionModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            ReportServices reportServices = new ReportServices();
            string strSql = @"UPDATE dbo.T_VegProsecution set ";
            strSql += "PFSC_Num = @PFSC_Num,";
            strSql += "PFSC_Cd = @PFSC_Cd,";
            strSql += "PFSC_Zy = @PFSC_Zy,";
            strSql += "PFSC_Qy = @PFSC_Qy,";
            strSql += "VegCompanyNum = @VegCompanyNum,";
            strSql += "ZzNum_Country = @ZzNum_Country,";
            strSql += "ZzNum_Pro = @ZzNum_Pro,";
            strSql += "ZzNum_City = @ZzNum_City,";
            strSql += "ZzNum_Area = @ZzNum_Area,";
            strSql += "ZzNum_Other = @ZzNum_Other,";
            strSql += "VegHzNum = @VegHzNum,";
            strSql += "VegNhNum = @VegNhNum,";
            strSql += "VegMjNum = @VegMjNum,"; 
             strSql += "VegHzSale = @VegHzSale,";
             strSql += "JTNC_Num = @JTNC_Num,";
            strSql += "JTNC_NMNum = @JTNC_NMNum,";
            strSql += "JTNC_Area = @JTNC_Area,";
            strSql += "JTNC_Sales = @JTNC_Sales,";
            strSql += "ZlrzNum_Sfx = @ZlrzNum_Sfx,";
            strSql += "ZlrzNum_Sfq = @ZlrzNum_Sfq,";
            strSql += "ZlrzNum_Cmsbs = @ZlrzNum_Cmsbs,";
            strSql += "ZlrzNum_Mpcp = @ZlrzNum_Mpcp,";
            strSql += "ZlrzNum_YnsZmsb = @ZlrzNum_YnsZmsb,";
            strSql += "ZlrzNum_WghNum = @ZlrzNum_WghNum,";
            strSql += "ZlrzNum_Wghmj = @ZlrzNum_Wghmj,";
            strSql += "ZlrzNum_Greencp = @ZlrzNum_Greencp,";
            strSql += "ZlrzNum_Greencpmj = @ZlrzNum_Greencpmj,";
            strSql += "ZlrzNum_Dlsb = @ZlrzNum_Dlsb,";
            strSql += "ZlrzNum_Dlbh = @ZlrzNum_Dlbh,";
            strSql += "Remark = @Remark,";
            strSql += "AddUser = @AddUser,";
            strSql += "AddTime = @AddTime,";
            strSql += "UpdateUser = @UpdateUser,";
            strSql += "UpdateTime = @UpdateTime";
            strSql += " where ReportId = @ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "数据更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新表4蔬菜外销调查表T_VegExport对象信息失败", ex);
            }
            return obj;
        }
        public ResResult<List<VegProsecutionStatsModel>> GetStatsList(VegNowModel model)
        {
            DictServices dictServices = new DictServices();
            ResResult<List<VegProsecutionStatsModel>> result = new ResResult<List<VegProsecutionStatsModel>>();
            var paramlist = new DynamicParameters();
            int OldFillInYear = 0;
            if (!string.IsNullOrEmpty(model.AreaCode))
            {
                paramlist.Add("AreaCode", model.AreaCode);
            }
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetKey("ReportYear", model.FillInYear); ;
                OldFillInYear = Convert.ToInt32(model.FillInYear) - 1;//上一年
                if (OldFillInYear < 0)
                {
                    OldFillInYear = 0;
                }
                paramlist.Add("FillInYear", model.FillInYear);
                paramlist.Add("OldFillInYear", OldFillInYear);
            }
            if (!string.IsNullOrEmpty(model.Status))
            {
                paramlist.Add("Status", model.Status);
            }
            #region 当年统计数据
            string strNowSql = "select Area.Code AreaCode,Dict.DictText FillYear, FullName AreaName,Coord,";
            strNowSql += "ISNULL(PFSC_Num, 0.00)  PFSC_Num,             "; //  --蔬菜市场个数
            strNowSql += "ISNULL(PFSC_Cd, 0.00)   PFSC_Cd,              "; //  --产地批发市场
            strNowSql += "ISNULL(PFSC_Zy, 0.00)   PFSC_Zy,               "; // --专业批发市场
            strNowSql += "ISNULL(PFSC_Qy, 0.00)   PFSC_Qy,      "; //--区域性综合批发市场
            strNowSql += "ISNULL(VegCompanyNum, 0.00) VegCompanyNum ,      "; //--二、蔬菜企业个数
            strNowSql += "ISNULL(ZzNum_Country, 0.00)  ZzNum_Country,       "; //--国家级龙头企业
            strNowSql += "ISNULL(ZzNum_Pro, 0.00) ZzNum_Pro ,      "; //--省级龙头企业
            strNowSql += "ISNULL(ZzNum_City, 0.00) ZzNum_City ,      "; //--州(市)级龙头企业
            strNowSql += "ISNULL(ZzNum_Area, 0.00) ZzNum_Area ,      "; //--县(市, 区)级龙头企业
            strNowSql += "ISNULL(ZzNum_Other, 0.00) ZzNum_Other ,      "; //--其他龙头企业
            strNowSql += "ISNULL(VegHzNum, 0.00) VegHzNum ,      "; //--蔬菜专业合作组织数量
            strNowSql += "ISNULL(VegNhNum, 0.00) VegNhNum ,     "; // --参加蔬菜农民合作社农户数
            strNowSql += "ISNULL(VegMjNum, 0.00) VegMjNum ,      "; //--蔬菜农民合作社经营面积
            strNowSql += "ISNULL(VegHzSale, 0.00) VegHzSale ,      "; //--蔬菜农民合作社年销售收入
            strNowSql += "ISNULL(JTNC_Num, 0.00) JTNC_Num ,      "; //--蔬菜家庭农场数量
            strNowSql += "ISNULL(JTNC_NMNum, 0.00) JTNC_NMNum ,     "; // --参加蔬菜家庭农场农户数
            strNowSql += "ISNULL(JTNC_Area, 0.00) JTNC_Area ,      "; //--蔬菜家庭农场经营面积(其中: 流转土地面积)
            strNowSql += "ISNULL(JTNC_Sales, 0.00) JTNC_Sales ,      "; //--蔬菜家庭农场年销售收入
            strNowSql += "ISNULL(ZlrzNum_Sfx, 0.00) ZlrzNum_Sfx ,      "; //--国家级农业标准化示范县数量
            strNowSql += "ISNULL(ZlrzNum_Sfq, 0.00) ZlrzNum_Sfq ,      "; //--出口食品农产品质量安全示范区数量
            strNowSql += "ISNULL(ZlrzNum_Cmsbs, 0.00) ZlrzNum_Cmsbs ,      "; //--中国驰名商标数
            strNowSql += "ISNULL(ZlrzNum_Mpcp, 0.00) ZlrzNum_Mpcp ,      "; //--中国名牌产品数
            strNowSql += "ISNULL(ZlrzNum_YnsZmsb, 0.00) ZlrzNum_YnsZmsb ,      "; //--云南省著名商标署
            strNowSql += "ISNULL(ZlrzNum_WghNum, 0.00) ZlrzNum_WghNum ,      "; //--无公害农产品数量
            strNowSql += "ISNULL(ZlrzNum_Wghmj, 0.00) ZlrzNum_Wghmj ,     "; // --无公害农产品认证面积
            strNowSql += "ISNULL(ZlrzNum_Greencp, 0.00) ZlrzNum_Greencp ,     "; // --绿色产品数
            strNowSql += "ISNULL(ZlrzNum_Greencpmj, 0.00) ZlrzNum_Greencpmj ,     "; // --绿色产品认证面积
            strNowSql += "ISNULL(ZlrzNum_Dlsb, 0.00) ZlrzNum_Dlsb ,     "; // --地理标志证明商标数
            strNowSql += "ISNULL(ZlrzNum_Dlbh, 0.00) ZlrzNum_Dlbh     "; //--地理标志产品保护认证数
            strNowSql += "from(    ";
            strNowSql += "select     ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strNowSql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode,    ";
            }
            else
            {
                strNowSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code,    ";
            }
            strNowSql += "SUM(ISNULL(VegProsecution.PFSC_Num,0.00))  PFSC_Num,      "; //         --蔬菜市场个数
            strNowSql += "SUM(ISNULL(VegProsecution.PFSC_Cd,0.00))   PFSC_Cd,        "; //        --产地批发市场
            strNowSql += "SUM(ISNULL(VegProsecution.PFSC_Zy,0.00))   PFSC_Zy,       "; //         --专业批发市场
            strNowSql += "SUM(ISNULL(VegProsecution.PFSC_Qy,0.00))   PFSC_Qy,      "; //--区域性综合批发市场
            strNowSql += "SUM(ISNULL(VegProsecution.VegCompanyNum,0.00)) VegCompanyNum ,     "; // --二、蔬菜企业个数
            strNowSql += "SUM(ISNULL(VegProsecution.ZzNum_Country,0.00))  ZzNum_Country,      "; // --国家级龙头企业
            strNowSql += "SUM(ISNULL(VegProsecution.ZzNum_Pro,0.00)) ZzNum_Pro ,     "; // --省级龙头企业
            strNowSql += "SUM(ISNULL(VegProsecution.ZzNum_City,0.00)) ZzNum_City ,     "; // --州(市)级龙头企业
            strNowSql += "SUM(ISNULL(VegProsecution.ZzNum_Area,0.00)) ZzNum_Area ,     "; // --县(市,区)级龙头企业
            strNowSql += "SUM(ISNULL(VegProsecution.ZzNum_Other,0.00)) ZzNum_Other ,     "; // --其他龙头企业
            strNowSql += "SUM(ISNULL(VegProsecution.VegHzNum,0.00)) VegHzNum ,     "; // --蔬菜专业合作组织数量
            strNowSql += "SUM(ISNULL(VegProsecution.VegNhNum,0.00)) VegNhNum ,     "; // --参加蔬菜农民合作社农户数
            strNowSql += "SUM(ISNULL(VegProsecution.VegMjNum,0.00)) VegMjNum ,      "; //--蔬菜农民合作社经营面积
            strNowSql += "SUM(ISNULL(VegProsecution.VegHzSale,0.00)) VegHzSale ,     "; // --蔬菜农民合作社年销售收入
            strNowSql += "SUM(ISNULL(VegProsecution.JTNC_Num,0.00)) JTNC_Num ,     "; // --蔬菜家庭农场数量
            strNowSql += "SUM(ISNULL(VegProsecution.JTNC_NMNum,0.00)) JTNC_NMNum ,      "; //--参加蔬菜家庭农场农户数
            strNowSql += "SUM(ISNULL(VegProsecution.JTNC_Area,0.00)) JTNC_Area ,      "; //--蔬菜家庭农场经营面积(其中:流转土地面积)
            strNowSql += "SUM(ISNULL(VegProsecution.JTNC_Sales,0.00)) JTNC_Sales ,      "; //--蔬菜家庭农场年销售收入
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Sfx,0.00)) ZlrzNum_Sfx ,     "; // --国家级农业标准化示范县数量
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Sfq,0.00)) ZlrzNum_Sfq ,      "; //--出口食品农产品质量安全示范区数量
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Cmsbs,0.00)) ZlrzNum_Cmsbs ,     "; // --中国驰名商标数
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Mpcp,0.00)) ZlrzNum_Mpcp ,      "; //--中国名牌产品数
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_YnsZmsb,0.00)) ZlrzNum_YnsZmsb ,      "; //--云南省著名商标署
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_WghNum,0.00)) ZlrzNum_WghNum ,     "; // --无公害农产品数量
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Wghmj,0.00)) ZlrzNum_Wghmj ,     "; // --无公害农产品认证面积
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Greencp,0.00)) ZlrzNum_Greencp ,     "; // --绿色产品数
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Greencpmj,0.00)) ZlrzNum_Greencpmj ,     "; // --绿色产品认证面积
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Dlsb,0.00)) ZlrzNum_Dlsb ,      "; //--地理标志证明商标数
            strNowSql += "SUM(ISNULL(VegProsecution.ZlrzNum_Dlbh,0.00)) ZlrzNum_Dlbh        "; //--地理标志产品保护认证数
            strNowSql += "from[dbo].[T_Report] Report    ";
            strNowSql += "left join [dbo].[T_VegProsecution] VegProsecution on Report.Id=VegProsecution.ReportId     ";
            strNowSql += "right join T_Area Area on Area.Code = Report.AreaCode    ";
            strNowSql += "where    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || model.AreaCode == "")
            {
                strNowSql += "Area.ParentCode! = @AreaCode    ";
            }
            else
            {
                strNowSql += "Area.ParentCode = @AreaCode    ";
            }
            strNowSql += "and Report.FillInYear = @FillInYear    ";
            strNowSql += "and Report.Status = @Status     "; //--表示审核通过
            if (model.AreaCode == DefaultKey.ProvinceCode || model.AreaCode == "")
            {
                strNowSql += "group by rollup(Area.ParentCode)    ";
            }
            else
            {
                strNowSql += "group by rollup(Area.Code)    ";
            }
            strNowSql += ") tt1    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || model.AreaCode == "")
            {
                strNowSql += "right join T_Area  Area on Area.Code=tt1.ParentCode       ";
            }
            else
            {
                strNowSql += "right join T_Area Area  on tt1.Code=Area.Code   ";
            }
            strNowSql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear    ";
            strNowSql += "where Area.ParentCode = @AreaCode  or Area.Code = @AreaCode    order by Area.Code ";
            #endregion
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //查询去年数据
                    result.model = dbCon.Query<VegProsecutionStatsModel>(strNowSql, paramlist).ToList();
                    result.status = DefaultKey.SuccessResult;//初始化成功status为1
                    result.message = "VegProsecution报表统计数据获取成功！";
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = "VegProsecution报表统计数据获取失败" + ex.ToString().Trim();
            }
            return result;
        }
        public ResResult<List<VegProsecutionStatsModel>> GetDetailStats(VegNowModel model)
        {
            ResResult<List<VegProsecutionStatsModel>> res = new ResResult<List<VegProsecutionStatsModel>>();
            List<VegProsecutionStatsModel> list = new List<VegProsecutionStatsModel>();
            string ProvinceCode = DefaultKey.ProvinceCode;
            string FillInYear = model.FillInYear;
            if (model.AreaCode == ProvinceCode)
            {
                var First = GetStatsList(model).model;
                First[0].AreaName = "合计";
                list.Add(First[0]);//省级汇总合计
                foreach (var item in First)
                {
                    if (item.AreaCode != ProvinceCode)
                    {
                        VegNowModel model2 = new VegNowModel();
                        model2.FillInYear = FillInYear;
                        model2.AreaCode = item.AreaCode;
                        model2.Status = model.Status;
                        var Second = GetStatsList(model2).model;
                        list.Add(item);
                        foreach (var item2 in Second)
                        {
                            if (model2.AreaCode != item2.AreaCode)
                            {
                                list.Add(item2);
                            }
                        }
                    }
                }
            }
            else
            {
                list = GetStatsList(model).model;//数据获取
            }
            if (list != null)
            {
                res.model = list;
                res.message = "获取统计数据详情成功";
                res.status = DefaultKey.SuccessResult;
            }
            else
            {
                res.message = "获取统计数据详情成失败";
                res.status = DefaultKey.ErrorResult;
            }
            return res;
        }
        public MemoryStream MethodStats(VegNowModel model)
        {
            string FillInYear = model.FillInYear;
            AreaServices areaServices = new AreaServices();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
            getExcelStatsList(workbook, model, AreaName);
            if (model.AreaCode == DefaultKey.ProvinceCode)//判断当前用户是否为省级用户
            {
                var areaList = areaServices.GetPrefectureList();//获取州市级列表
                foreach (var areaCode in areaList)
                {
                    model.AreaCode = areaCode;
                    model.FillInYear = FillInYear;
                    getExcelStatsList(workbook, model, areaServices.GetByCodeModel(areaCode).model.Name);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 返回excel工作簿
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheet">子表</param>
        /// <param name="model"></param>
        /// <param name="AreaName">地区名称</param>
        /// <returns></returns>
        public HSSFWorkbook getExcelStatsList(HSSFWorkbook workbook, VegNowModel model, string AreaName)
        {
            FileStream file = null;
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegStatsDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("VegProsecution") as HSSFSheet;//读取模板所在sheet位置
            List<VegProsecutionStatsModel> list = new List<VegProsecutionStatsModel>();
            list = null;
            list = GetDetailStats(model).model;
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式
            if (list != null)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 4);
                    style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                    if (i == 0)
                    {
                        list[0].AreaName = "合计";
                        datarow.RowStyle = style;
                    }
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());//地区
                    datarow.CreateCell(1).SetCellValue(list[i].PFSC_Num == null ? "" : Common.GetADecimal(list[i].PFSC_Num));//常年蔬菜占地面积
                    datarow.CreateCell(2).SetCellValue(list[i].PFSC_Cd == null ? "" : Common.GetADecimal(list[i].PFSC_Cd));
                    datarow.CreateCell(3).SetCellValue(list[i].PFSC_Zy == null ? "" : Common.GetADecimal(list[i].PFSC_Zy));//冬春季蔬菜面积
                    datarow.CreateCell(4).SetCellValue(list[i].PFSC_Qy == null ? "" : Common.GetADecimal(list[i].PFSC_Qy));
                    datarow.CreateCell(5).SetCellValue(list[i].VegCompanyNum == null ? "" : Common.GetADecimal(list[i].VegCompanyNum));//春夏季蔬菜面积
                    datarow.CreateCell(6).SetCellValue(list[i].ZzNum_Country == null ? "" : Common.GetADecimal(list[i].ZzNum_Country));//
                    datarow.CreateCell(7).SetCellValue(list[i].ZzNum_Pro == null ? "" : Common.GetADecimal(list[i].ZzNum_Pro));//菜地喷滴灌面积
                    datarow.CreateCell(8).SetCellValue(list[i].ZzNum_City == null ? "" : Common.GetADecimal(list[i].ZzNum_City));//
                    datarow.CreateCell(9).SetCellValue(list[i].ZzNum_Area == null ? "" : Common.GetADecimal(list[i].ZzNum_Area));//
                    datarow.CreateCell(10).SetCellValue(list[i].ZzNum_Other == null ? "" : Common.GetADecimal(list[i].ZzNum_Other));//
                    datarow.CreateCell(11).SetCellValue(list[i].VegHzNum == null ? "" : Common.GetADecimal(list[i].VegHzNum));//
                    datarow.CreateCell(12).SetCellValue(list[i].VegNhNum == null ? "" : Common.GetADecimal(list[i].VegNhNum));//
                    datarow.CreateCell(13).SetCellValue(list[i].VegMjNum == null ? "" : Common.GetADecimal(list[i].VegMjNum));//
                    datarow.CreateCell(14).SetCellValue(list[i].VegHzSale == null ? "" : Common.GetADecimal(list[i].VegHzSale));//
                    datarow.CreateCell(15).SetCellValue(list[i].JTNC_Num == null ? "" : Common.GetADecimal(list[i].JTNC_Num));//
                    datarow.CreateCell(16).SetCellValue(list[i].JTNC_NMNum == null ? "" : Common.GetADecimal(list[i].JTNC_NMNum));//
                    datarow.CreateCell(17).SetCellValue(list[i].JTNC_Area == null ? "" : Common.GetADecimal(list[i].JTNC_Area));//
                    datarow.CreateCell(18).SetCellValue(list[i].JTNC_Sales == null ? "" : Common.GetADecimal(list[i].JTNC_Sales));//
                    datarow.CreateCell(19).SetCellValue(list[i].ZlrzNum_Sfx == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Sfx));//
                    datarow.CreateCell(20).SetCellValue(list[i].ZlrzNum_Sfq == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Sfq));//
                    datarow.CreateCell(21).SetCellValue(list[i].ZlrzNum_Cmsbs == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Cmsbs));//
                    datarow.CreateCell(22).SetCellValue(list[i].ZlrzNum_Mpcp == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Mpcp));//
                    datarow.CreateCell(23).SetCellValue(list[i].ZlrzNum_YnsZmsb == null ? "" : Common.GetADecimal(list[i].ZlrzNum_YnsZmsb));//
                    datarow.CreateCell(24).SetCellValue(list[i].ZlrzNum_WghNum == null ? "" : Common.GetADecimal(list[i].ZlrzNum_WghNum));//
                    datarow.CreateCell(25).SetCellValue(list[i].ZlrzNum_Wghmj == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Wghmj));//
                    datarow.CreateCell(26).SetCellValue(list[i].ZlrzNum_Greencp == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Greencp));//
                    datarow.CreateCell(27).SetCellValue(list[i].ZlrzNum_Greencpmj == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Greencpmj));//
                    datarow.CreateCell(28).SetCellValue(list[i].ZlrzNum_Dlsb == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Dlsb));//
                    datarow.CreateCell(29).SetCellValue(list[i].ZlrzNum_Dlbh == null ? "" : Common.GetADecimal(list[i].ZlrzNum_Dlbh));//
                    //datarow.GetCell(0).CellStyle = style;
                }
                sheet.CopyTo(workbook, AreaName, true, true);
            }
            return workbook;
        }
    }
}